package ref;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import ref.AAnalysisHandle;
import ref.OracleConn;

public class OTCchinesemedicine  extends AAnalysisHandle {
	
	private String targetTable = "otc_chinese_medicine";
	
	public String getHandleName() {
		return "OTC中药";
	}

	public String getTargetTable() {
		return targetTable;
	}
	
	public void handleTransfer(String path, Connection oracleConn)throws Exception {
		PreparedStatement pstmt = null;
		try{
			int index = 0;
			pstmt = oracleConn.prepareStatement("insert into "+ targetTable +" values(?,?,?,?,?,?)");
			oracleConn.setAutoCommit(false);
			InputStream is = new FileInputStream(new File(path));
			Workbook workbook = WorkbookFactory.create(is);
			Sheet sheet = workbook.getSheetAt(0);
			Row row ;
				for(int i = 1; i <= sheet.getLastRowNum(); i++) {
					row = sheet.getRow(i);
					pstmt.setInt(1, (int) row.getCell(3).getNumericCellValue());
					pstmt.setString(2, row.getCell(0).getStringCellValue().trim());
					pstmt.setString(3, row.getCell(1).getStringCellValue().trim());
					pstmt.setString(4, row.getCell(2).getStringCellValue().trim());
					pstmt.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
					pstmt.setInt(6, (int) row.getCell(4).getNumericCellValue());
					pstmt.addBatch();
					index++;
					
					if(index % 1000 == 0) {
						pstmt.executeBatch();
						OracleConn.log("宸插鐞嗐�" + index + "銆戞暟鎹�");
					}
				}
			is.close();
			pstmt.executeBatch();
			oracleConn.commit();
			oracleConn.setAutoCommit(true);
			OracleConn.log("鍏卞鐞嗐�" + index + "銆戞暟鎹�");
		} catch (Exception e) {
			e.printStackTrace(System.err);
			try {
				oracleConn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace(System.err);
			}
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}
}